﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;

namespace WAF.Data
{
    #region EntityBase

    /// <summary>
    /// 实体类基类，完成增，删，改，查等常用功能
    /// 利用反射读取实体类字段信息，并生成SQL语句发送到数据库执行
    /// 实体类对应的数据库表有且只能有一个主键
    /// 
    /// 注：如果使用MySql数据库，且主键用GUID格式，则应在连接字符串后添加"Old Guids=true"选项，以防止空GUID字段转换失败的错误
    /// 
    /// 实体类需要设置成以下格式
    /// class EntityClass : WAF.Data.EntityBase
    /// {
    ///     [EntityFieldAttribute(true, false, DbFieldType.string, 32)]
    ///     public string FieldId;
    ///
    ///     [EntityFieldAttribute(false, false, DbFieldType.String, 32, "RelationTable", "relationPkField", "relationNameField")]
    ///     public string FieldName;
    /// }
    /// </summary>
    public class EntityBase
    {
        #region Insert

        /// <summary>
        /// 向数据库中增加一条实体类记录
        /// </summary>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>如果该实体已存在则返回false, 否则返回true</returns>
        public bool Insert(DbAccess dbAccess = null)
        {
            Type entityType = this.GetType();
            if (EntityBase.Exist(entityType, getPkValue(this), dbAccess)) return false;

            PropertyInfo[] fieldInfo = entityType.GetProperties();

            StringBuilder sbField = (new StringBuilder()).Append("insert into ").Append(entityType.Name).Append("(");
            StringBuilder sbValue = new StringBuilder(" values(");
            DbParameters parms = new DbParameters();

            foreach (PropertyInfo fInfo in fieldInfo)
            {
                EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);

                //跳过主键
                if (fAttribute.IsPrimaryKey) continue;

                sbField.Append(fInfo.Name).Append(",");
                sbValue.Append("@").Append(fInfo.Name).Append(",");

                parms.Add(fInfo.Name, fInfo.GetValue(this, null), fAttribute.DbFieldType);
            }

            string sql = sbField.Replace(',', ')', sbField.Length - 1, 1).ToString() +
                         sbValue.Replace(',', ')', sbValue.Length - 1, 1).ToString();

            if (dbAccess == null)
                DbAccess.ExeNonQuery(sql, parms);
            else
                dbAccess.ExecuteNonQuery(sql, parms);

            return true;
        }

        #endregion

        #region Update

        /// <summary>
        /// 向数据库中更新相关实体类记录
        /// </summary>
        /// <param name="dbAccess">数据库访问组件</param>
        public void Update(DbAccess dbAccess = null)
        {
            string pkName = "";
            Type entityType = this.GetType();
            PropertyInfo[] fieldInfo = entityType.GetProperties();

            DbParameters parms = new DbParameters();
            StringBuilder sb = (new StringBuilder()).Append("update ").Append(entityType.Name).Append(" set ");

            foreach (PropertyInfo fInfo in fieldInfo)
            {
                EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);

                //跳过主键
                if (fAttribute.IsPrimaryKey)
                {
                    pkName = fInfo.Name;
                    parms.Add(fInfo.Name, fInfo.GetValue(this, null), fAttribute.DbFieldType);
                    continue;
                }

                sb.Append(fInfo.Name).Append("=").Append("@").Append(fInfo.Name).Append(",");
                parms.Add(fInfo.Name, fInfo.GetValue(this, null), fAttribute.DbFieldType);
            }

            string sql = sb.Replace(',', ' ', sb.Length - 1, 1).Append(" where ").Append(pkName).Append("=@").Append(pkName).ToString();

            if (dbAccess == null)
                DbAccess.ExeNonQuery(sql, parms);
            else
                dbAccess.ExecuteNonQuery(sql, parms);
        }

        #endregion

        #region Delete

        /// <summary>
        /// 向数据库中删除一条实体类记录
        /// </summary>
        /// <param name="entityId">实体ID</param>
        /// <param name="dbAccess">数据库访问组件</param>
        public static void Delete(Type entityType, object entityId, DbAccess dbAccess = null)
        {
            DbParameters parms = new DbParameters();

            string sql = (new StringBuilder()).Append("delete from ").Append(entityType.Name).Append(getPkWhereSQL(entityType, entityId, parms)).ToString();

            if (dbAccess == null)
                DbAccess.ExeNonQuery(sql, parms);
            else
                dbAccess.ExecuteNonQuery(sql, parms);
        }

        #endregion

        #region Exist

        /// <summary>
        /// 查询数据库中相应实体类记录是否存在
        /// </summary>
        /// <param name="entityId">实体ID</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>存在返回true,不存在返回false</returns>
        public static bool Exist(Type entityType, object entityId, DbAccess dbAccess = null)
        {
            DbParameters parms = new DbParameters();
            string sql = (new StringBuilder()).Append("select count(*) from ").Append(entityType.Name).Append(getPkWhereSQL(entityType, entityId, parms)).ToString();

            object obj = (dbAccess == null ? DbAccess.ExeScalar(sql, parms) : dbAccess.ExecuteScalar(sql, parms));

            bool isExist = int.Parse(obj.ToString()) >= 1 ? true : false;

            return isExist;
        }

        /// <summary>
        /// 查询数据库中相应实体类记录是否存在
        /// </summary>
        /// <param name="entityType">实体类类型</param>
        /// <param name="uniqueFieldName">要检查的字段名</param>
        /// <param name="uniqueFieldValue">要检查的字段值</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>存在返回true,不存在返回false</returns>
        public static bool Exist(Type entityType, string uniqueFieldName, object uniqueFieldValue, DbAccess dbAccess = null)
        {
            EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(entityType.GetProperty(uniqueFieldName));
            DbParameters parms = new DbParameters("@parm", uniqueFieldValue, fAttribute.DbFieldType);

            string sql = (new StringBuilder()).Append("select count(*) from ").Append(entityType.Name)
                                              .Append(" where ").Append(uniqueFieldName).Append("=@parm").ToString();

            object obj = (dbAccess == null ? DbAccess.ExeScalar(sql, parms) : dbAccess.ExecuteScalar(sql, parms));

            bool isExist = int.Parse(obj.ToString()) >= 1 ? true : false;

            return isExist;
        }

        #endregion

        #region Count

        /// <summary>
        /// 数据库表中的记录总数
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>记录总数</returns>
        public static int Count(Type entityType, DbAccess dbAccess = null)
        {
            string sql = (new StringBuilder()).Append("select count(*) from ").Append(entityType.Name).ToString();
            object obj = (dbAccess == null ? DbAccess.ExeScalar(sql) : dbAccess.ExecuteScalar(sql));
            return int.Parse(obj.ToString());
        }

        #endregion

        #region GetRelationName

        /// <summary>
        /// 获取关联表的名称字段值
        /// </summary>
        /// <param name="relId">此实体类中的关联字段</param>
        /// <returns>名称字段值</returns>
        public string GetRelationName(string relId)
        {
            if (RelationName == null)
            {
                return "";
            }
            else
            {
                string relValue = "";
                RelationName.TryGetValue(relId, out relValue);
                return relValue;
            }
        }

        #endregion

        #region GetFieldDeclare

        /// <summary>
        /// 获取字段说明
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="fieldName">字段名称</param>
        /// <returns>字段说明</returns>
        public static string GetFieldDeclare(Type entityType, string fieldName)
        {
            string declare = "";

            foreach (PropertyInfo fInfo in entityType.GetProperties())
            {
                if (fInfo.Name == fieldName)
                {
                    EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);
                    declare = fAttribute.Declare;
                    break;
                }
            }

            return declare;
        }

        #endregion

        #region GetPropertesOrderBy

        /// <summary>
        /// 返回按类中定义顺序排序的属性数组，下标从零开始
        /// </summary>
        /// <param name="entityType">实体类类型</param>
        /// <returns>排序后的属性数组</returns>
        public static PropertyInfo[] GetPropertiesOrder(Type entityType)
        {
            PropertyInfo[] pInfos = entityType.GetProperties();
            PropertyInfo[] orderPropertyInfo = new PropertyInfo[pInfos.Length];

            foreach (PropertyInfo fInfo in pInfos)
            {
                EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);

                orderPropertyInfo[fAttribute.Sequence] = fInfo;
            }

            return orderPropertyInfo;
        }

        #endregion

        #region GetCustomAttributes
        
        /// <summary>
        /// 返回指定字段的自定义属性
        /// </summary>
        /// <param name="fInfo"></param>
        /// <returns></returns>
        public static EntityFieldAttribute GetCustomAttributes(PropertyInfo fInfo)
        {
            return fInfo.GetCustomAttributes(typeof(EntityFieldAttribute), false)[0] as EntityFieldAttribute;
        }

        #endregion

        #region ValidateField

        /// <summary>
        /// 检查字段的合法性
        /// </summary>
        /// <param name="pInfo"></param>
        /// <param name="fieldValue"></param>
        /// <returns></returns>
        public static string ValidateField(PropertyInfo pInfo, object fieldValue)
        {
            if (fieldValue == null) return "空引用！";

            string validateResult = ""; //验证结果
            EntityFieldAttribute fAttribute = pInfo.GetCustomAttributes(typeof(EntityFieldAttribute), false)[0] as EntityFieldAttribute;

            //检查字段是否能为空
            if (fAttribute.IsNotNull && (System.DBNull.Value.Equals(fieldValue) || fieldValue.ToString().Trim() == ""))
                validateResult = fAttribute.Declare + " 不能为空！";
            //检查整数字段
            else if(fAttribute.DbFieldType == DbType.Int32)
                { int result; validateResult = (int.TryParse(fieldValue.ToString(), out result) ? "" : "输入的整数不合法！"); }
            //检查长整数字段
            else if(fAttribute.DbFieldType == DbType.Int64)
                { long result; validateResult = (long.TryParse(fieldValue.ToString(), out result) ? "" : "输入的长整数不合法！"); }
            //检查浮点数字段
            else if(fAttribute.DbFieldType == DbType.Double)
                { double result; validateResult = (double.TryParse(fieldValue.ToString(), out result) ? "" : "输入的浮点数不合法！"); }
            //检查字符类型字段的长度
            else if (fAttribute.DbFieldType == DbType.String && fieldValue.ToString().Length > fAttribute.DbFieldSize )
                validateResult = "超过该字段最大长度（" + fAttribute.DbFieldSize.ToString() + "）";
            //检查字段是否唯一
            else if (fAttribute.IsUnique && EntityBase.Exist(pInfo.ReflectedType, pInfo.Name, fieldValue))
                validateResult = "该记录已经存在！";

            return validateResult;
        }

        #endregion

        #region Load

        /// <summary>
        /// 创建实体类并获取实体类各字段值
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="entityId">主键值</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>实体类</returns>
        public static T Load<T>(object entityId, DbAccess dbAccess = null) where T : EntityBase
        {
            Type entityType = typeof(T);
            DbParameters parms = new DbParameters();

            string sql = (new StringBuilder()).Append("select * from ").Append(entityType.Name).Append(getPkWhereSQL(entityType, entityId, parms)).ToString();

            T[] entitys = createEntity<T>(sql, parms, dbAccess);

            if (entitys.Length == 0)
                return null;
            else
                return entitys[0];
        }

        #endregion

        #region Search

        /// <summary>
        /// 搜索制定字段满足搜索条件的实体类数组
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="searchField">搜索的字段</param>
        /// <param name="searchTxt">搜索字符串</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>实体类数组</returns>
        public static T[] Find<T>(string sql = "",  DbAccess dbAccess = null) where T : EntityBase
        {
            Type entityType = typeof(T);
            if (sql == "") sql = "select * from " + entityType.Name;

            return createEntity<T>(sql, null, dbAccess);
        }

        /// <summary>
        /// 创建满足搜索条件的实体类DataTable,只模糊查询要显示的字段和关联字段
        /// </summary>
        /// <param name="entityType">实体类名称</typeparam>
        /// <param name="searchTxt">搜索字符串</param>
        /// <param name="viewIdField">是否将ID字段显示为其对应的名称字段（默认为显示名称字段）</param>
        /// <param name="viewField">要显示的字段列表</param>
        /// <param name="pageNo">当前页数</param>
        /// <param name="pageSzie">每页记录数</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>实体类DataTable</returns>
        public static DataTable Search(Type entityType, string searchTxt, bool viewIdField = false, string[] viewField = null, int pageNo = 0, int pageSzie = 0, DbAccess dbAccess = null)
        {
            string tbName = entityType.Name;
            List<PropertyInfo> listFields = new List<PropertyInfo>();

            foreach (PropertyInfo p in entityType.GetProperties())
            {
                if (viewField != null && !viewField.Contains(p.Name)) continue;
                
                listFields.Add(p);
            }

            StringBuilder sbSelect = new StringBuilder("select ");
            StringBuilder sbFrom = new StringBuilder(" from " + tbName);
            StringBuilder sbWhere = new StringBuilder(" where ");

            //遍历显示字段，生成SQL语句
            foreach (PropertyInfo pInfo in listFields)
            {
                string fieldName = tbName + "." + pInfo.Name;
                EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(pInfo);

                if (fAttribute.RelationTable == "")
                {
                    sbSelect.Append(fieldName).Append(" as ").Append(pInfo.Name).Append(",");
                    sbWhere.Append(fieldName).Append(" like @search or ");
                }
                else
                {
                    if (viewIdField)
                        sbSelect.Append(fieldName).Append(" as ").Append(pInfo.Name).Append(",");
                    else
                        sbSelect.Append(fAttribute.RelationTable).Append(".").Append(fAttribute.RelationNameField).Append(" as ").Append(pInfo.Name).Append(",");

                    sbFrom.Append(" join ").Append(fAttribute.RelationTable)
                          .Append(" on ").Append(fieldName).Append("=").Append(fAttribute.RelationTable).Append(".").Append(fAttribute.RelationIdField);

                    sbWhere.Append(fAttribute.RelationTable).Append(".").Append(fAttribute.RelationNameField).Append(" like @search or ");
                }
            }

            //组合SQL语句
            sbSelect.Replace(",", "", sbSelect.Length - 1, 1);
            sbWhere.Replace("or ", "", sbWhere.Length - 3, 3);
            sbSelect.Append(sbFrom.ToString()).Append(sbWhere.ToString());

            //分页
            if (pageNo != 0 && pageSzie != 0)
            {
                int pageOffset = (pageNo - 1) * pageSzie;
                sbSelect.Append(" limit ").Append(pageOffset).Append(",").Append(pageSzie);
            }

            //查询参数
            DbParameters parms = new DbParameters();
            parms.Add("@search", "%" + searchTxt + "%", DbType.String);

            //返回的结果集
            DataTable tb = new DataTable();

            //执行SQL语句
            if (dbAccess == null)
                tb = DbAccess.ExeDataSet(sbSelect.ToString(), parms).Tables[0];
            else
                tb = dbAccess.ExecuteDataSet(sbSelect.ToString(), parms).Tables[0];

            return tb;
        }

        #endregion

        #region private

        /// <summary>
        /// 创建满足搜索条件的实体类数组
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="sql">搜索SQL语句</param>
        /// <param name="parms">参数</param>
        /// <param name="dbAccess">数据库访问组件</param>
        /// <returns>实体类数组</returns>
        private static T[] createEntity<T>(string sql, DbParameters parms, DbAccess dbAccess = null) where T : EntityBase
        {
            Type entityType = typeof(T);
            PropertyInfo[] fieldInfo = entityType.GetProperties();

            DataTable tb = (dbAccess == null) ? DbAccess.ExeDataSet(sql, parms).Tables[0] : dbAccess.ExecuteDataSet(sql, parms).Tables[0];

            int count = tb.Rows.Count;
            T[] entitys = new T[count];

            for (int i = 0; i < count; i++)
            {
                //创建实体类
                entitys[i] = (T)Activator.CreateInstance(entityType);

                foreach (PropertyInfo fInfo in fieldInfo)
                {
                    object obj = tb.Rows[i][fInfo.Name];
                    EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);

                    //如果数据库中字段的值为null，就将字符串字段值设置为空字符串，以防止引用错误；其他类型字段设为初始化值（Int:0，Float:0, DateTime:"0001-01-01 00:00:00"）
                    if (System.DBNull.Value.Equals(obj))
                    {
                        if (fAttribute.DbFieldType == DbType.String)
                            obj = (object)"";
                        else
                            continue;
                    }

                    //为属性设置值
                    fInfo.SetValue(entitys[i], obj, null);

                    //如果存在关联字段则获取关联表的名称字段值
                    if (fAttribute.RelationTable != "")
                    {
                        if (entitys[i].RelationName == null) entitys[i].RelationName = new Dictionary<string, string>();

                        string relsql = (new StringBuilder()).Append("select ").Append(fAttribute.RelationNameField)
                                                             .Append(" from ").Append(fAttribute.RelationTable)
                                                             .Append(" where ").Append(fAttribute.RelationIdField)
                                                             .Append("=@").Append(fAttribute.RelationIdField).ToString();

                        DbParameters relParms = new DbParameters();
                        relParms.Add(fAttribute.RelationIdField, obj, fAttribute.DbFieldType);
                        object relValue = (dbAccess == null ? DbAccess.ExeScalar(relsql, relParms) : dbAccess.ExecuteScalar(relsql, relParms));
                        
                        entitys[i].RelationName.Add(fInfo.Name, (relValue == null ? "无关联记录" : relValue.ToString()));
                    }
                }
            }

            return entitys;
        }

        /// <summary>
        /// 获取主键的WHERE语句
        /// </summary>
        /// <returns>WHERE语句</returns>
        private static string getPkWhereSQL(Type entityType, object pkValue, DbParameters parms)
        {
            string pkName = "";
            PropertyInfo[] fieldInfo = entityType.GetProperties();

            foreach (PropertyInfo fInfo in fieldInfo)
            {
                EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);

                if (fAttribute.IsPrimaryKey)
                {
                    pkName = fInfo.Name;
                    parms.Add(pkName, pkValue, fAttribute.DbFieldType);
                    break;
                }
            }

            StringBuilder sb = (new StringBuilder()).Append(" where ").Append(pkName).Append("=@").Append(pkName);

            return sb.ToString();
        }

        /// <summary>
        /// 获取指定实体类对象的主键值
        /// </summary>
        /// <param name="obj">实体类对象</param>
        /// <returns>主键值</returns>
        private string getPkValue(object obj)
        {
            string pkValue = null;
            PropertyInfo[] fieldInfo = obj.GetType().GetProperties();

            foreach (PropertyInfo fInfo in fieldInfo)
            {
                EntityFieldAttribute fAttribute = EntityBase.GetCustomAttributes(fInfo);

                if (fAttribute.IsPrimaryKey)
                {
                    pkValue = fInfo.GetValue(obj, null).ToString();
                    break;
                }
            }

            return pkValue;
        }

        /// <summary>
        /// 存储关联字段的名称值
        /// </summary>
        private Dictionary<string, string> RelationName;

        #endregion

        #region Override

        public override bool Equals(object obj)
        {
            return getPkValue(this) == getPkValue(obj) ? true : false;
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        #endregion
    }

    #endregion

    #region EntityFieldAttribute

    /// <summary>
    /// 实体类字段属性类，用于表示实体类字段对应数据库表中的属性，只能使用自增长INTEGER类型作为主键。
    /// </summary>
    [System.AttributeUsage(System.AttributeTargets.All, AllowMultiple = false, Inherited = false)]
    public class EntityFieldAttribute : System.Attribute
    {
        private int    sequence;            //字段在类中的顺序
        private bool   isPrimaryKey;        //是否为主键
        private bool   isUnique;            //是否为唯一键
        private bool   isNotNull;           //是否可为空
        private bool   isForeignKey;        //是否为外键
        private DbType dbFieldType;         //字段数据类型
        private int    fieldSize;           //字段长度
        private string declare;             //字段说明
        private string relationTable;       //关联表的名称
        private string relationIdField;     //关联的主键字段
        private string relationNameField;   //关联的名称字段

        public EntityFieldAttribute(int pSequence, bool pIsPk, bool pIsUnique, bool pIsNotNull, bool pIsForeignKey, DbType dbtype, int pFieldSize, string declaretxt, string relTable = "", string relField = "", string relNameField = "")
        {
            sequence            = pSequence;
            isPrimaryKey        = pIsPk;
            isUnique            = pIsUnique;
            isNotNull           = pIsNotNull;
            isForeignKey        = pIsForeignKey;
            dbFieldType         = dbtype;
            fieldSize           = pFieldSize;
            declare             = declaretxt;
            relationTable       = relTable;
            relationIdField     = relField;
            relationNameField   = relNameField;
        }

        public int    Sequence { get { return sequence; } }
        public bool   IsPrimaryKey { get { return isPrimaryKey; } }
        public bool   IsUnique { get { return isUnique; } }
        public bool   IsNotNull { get { return isNotNull; } }
        public bool   IsForeignKey { get { return isForeignKey; } }
        public DbType DbFieldType { get { return dbFieldType; } }
        public int    DbFieldSize { get { return fieldSize; } }
        public string Declare { get { return declare; } }
        public string RelationTable { get { return relationTable; } }
        public string RelationIdField { get { return relationIdField; } }
        public string RelationNameField { get { return relationNameField; } }
    }

    #endregion
}
